package com.chinaoryx.er;

import com.mysql.jdbc.Driver;
import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.dom4j.DocumentException;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: hyman.zhang
 * Date: 12-1-28
 * Time:
 */
public class LoadSchemaTestUnit {

    static Logger logger = Logger.getLogger(LoadSchemaTestUnit.class);

    EntityDefination entity = null;

    Connection conn1 = null;
    Connection conn2 = null;
    private String toUrl;
    private String toUsername;
    private String toPassword;
    private String url;
    private String username;
    private String password;

    private Configuration config;


    @Before
    public void setup() throws ConfigurationException, SQLException, DocumentException {

        ConfigReader reader = new ConfigReader();
//        URL url = new URL("entity.xml");
        entity = reader.readEntity("xxx", "F:\\GoogleHost\\database-transfer\\TaskManager\\src\\main\\config\\entity.xml");
        logger.info(entity);
//        entity = new EntityDefination();
//
//        TableColumn pk = new TableColumn();
//        pk.setColumnName("SO_DN_HEAD_OID");
//        pk.setType("String");
//        entity.setPrimaryKey(pk);
//
//        TableDefination pritable = new TableDefination();
//        entity.setTable(pritable);
//        pritable.setTableName("so_dn_head");
//
//
////        pritable.
//        pritable.setColumnMap(null);
//        TableDefination.ColumnMap cm = null;
//
//
//        List<TableDefination> subTables = new ArrayList<TableDefination>();
//        pritable.setSubTables(subTables);
//
//        TableDefination table2 = null;
//        table2 = new TableDefination();
//        table2.setTableName("so_dn_detail");
//        cm = new TableDefination.ColumnMap();
//        cm.setColumnName("so_dn_head_oid");
//        cm.setParentColumnName("so_dn_head_oid");
//        table2.setColumnMap(cm);
//        table2.setParentTable(pritable);
//
//
//        List<TableDefination> subSubTables = new ArrayList<TableDefination>();
//        TableDefination table3 = null;
//        table3 = new TableDefination();
//        table3.setTableName("so_dn_cost_detail");
//        cm = new TableDefination.ColumnMap();
//        cm.setColumnName("so_dn_detail_oid");
//        cm.setParentColumnName("so_dn_detail_oid");
//        table3.setColumnMap(cm);
//        table3.setParentTable(table2);
//        subSubTables.add(table3);
//        table2.setSubTables(subSubTables);
//
//        subTables.add(table2);
//
//
//
//        table2 = new TableDefination();
//        table2.setTableName("so_so_charge_item");
//        cm = new TableDefination.ColumnMap();
//        cm.setColumnName("so_dn_head_oid");
//        cm.setParentColumnName("so_dn_head_oid");
//        table2.setColumnMap(cm);
//        table2.setParentTable(pritable);
//
//
//        subTables.add(table2);


        config = new PropertiesConfiguration("config.properties");

      url = config.getString("db.url");
        username = config.getString("db.username");
        password = config.getString("db.password");

        toUrl = config.getString("to.db.url");
        toUsername = config.getString("to.db.username");
        toPassword = config.getString("to.db.password");

        DriverManager.registerDriver(new Driver());

        conn1 = DriverManager.getConnection(url, username, password);
        conn2 = DriverManager.getConnection(toUrl, toUsername, toPassword);



    }

    @Test
    public void testLoadSchema() throws ConfigurationException, SQLException, IOException {

        Configuration config = new PropertiesConfiguration("config.properties");

        String url = config.getString("db.url");
        String username = config.getString("db.username");
        String password = config.getString("db.password");

        DriverManager.registerDriver(new Driver());
        Connection conn = DriverManager.getConnection(url, username, password);
        entity.loadSchema(conn);

        conn.close();
    }

    @Test
    public void testDataTransfer() throws SQLException {

        entity.loadSchema(conn1);
        DataTransfer transfer = new DataTransfer();


        // remove the fk restrict
        Statement stmt = conn2.createStatement();
        stmt.execute("set foreign_key_checks=0");
        stmt.close();

        String sql = entity.getSqlForList();
        List<Map> list = new ArrayList<Map>();
        stmt = null;
        ResultSet rs = null;

        stmt = conn1.createStatement();
        rs = stmt.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        int c = 0;

        List<Thread> threadList = new ArrayList<Thread>();
        List<Connection> connList = new ArrayList<Connection>();
        while (rs.next()) {
            Map<String, Object> result = new HashMap<String, Object>();

            for (int i = 0; i<count; i++) {
                String columnLabel = rsmd.getColumnLabel(i+1);
                Object val = rs.getObject(i+1) ;
                result.put(columnLabel, val);

            }
            list.add(result);

        }


        rs.close();
        stmt.close();



        long time1 = System.currentTimeMillis();
        transfer.moveEntityList(conn1, conn2, entity, list);
        long time2 = System.currentTimeMillis();
        System.out.println("time:");
        System.out.println((time2-time1)/1000);
//        transfer.transfer();

    }

    @Test
    public void testDataTransferMultiThread() throws SQLException, InterruptedException {

        entity.loadSchema(conn1);


        BasicDataSource frmDs = new BasicDataSource();
        frmDs.setDriverClassName("com.mysql.jdbc.Driver");
        frmDs.setUsername(username);
        frmDs.setPassword(password);
        frmDs.setUrl(url);
        frmDs.setInitialSize(10);
        frmDs.setMaxActive(15);


        BasicDataSource toDs = new BasicDataSource();
        toDs.setDriverClassName("com.mysql.jdbc.Driver");
        toDs.setUsername(toUsername);
        toDs.setPassword(toPassword);
        toDs.setUrl(toUrl);
        toDs.setInitialSize(10);
        toDs.setMaxActive(15);


        long time1 = System.currentTimeMillis();
        TransferThreadManager threadManager = new TransferThreadManager();
        String abc = config.getString("thread.count");
        logger.info(abc);
        int threadCount = config.getInt("thread.count");
        int batchCount = config.getInt("batch.count");

        threadManager.moveEntityList(frmDs, toDs, entity,threadCount, batchCount);
        long time2 = System.currentTimeMillis();
        System.out.println("time:");
        System.out.println((time2-time1)/1000);


    }


    @Test
    public void testDeleteEntity() throws SQLException {
        entity.loadSchema(conn2);

        String sql = entity.getSqlForList();
        List<Map> list = new ArrayList<Map>();
        Statement stmt = null;
        ResultSet rs = null;

        stmt = conn2.createStatement();
        rs = stmt.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();

        while (rs.next()) {
            Map<String, Object> result = new HashMap<String, Object>();

            for (int i = 0; i<count; i++) {
                String columnLabel = rsmd.getColumnLabel(i+1);
                Object val = rs.getObject(i+1) ;
                result.put(columnLabel, val);

            }
            list.add(result);
        }
        
        
        
        for (Map<String, Object> record: list) {
            entity.deleteEntity(conn2, record);
        }


        rs.close();
        stmt.close();

    }
    
     @After
    public void after() throws SQLException {
        if (this.conn1!=null) {
            this.conn1.close();
        }

        if (this.conn2 != null) {
            this.conn2.close();
        }
    }

}
